/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import com.caracol.db.AccesoDatos;
import com.caracol.db.CaracolQueryGenerator;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * @author Roquet
 */
public class Escolaridad {
    
    private static CaracolQueryGenerator caracolQuery = new CaracolQueryGenerator();
    
    private int id;
    private int idGradoAcademico;
    private String folio;
    private String institutoExpedicion;
    private String certificado;
    private double promedio;
    private String titulo;
    private String fechaTermino;
    
    private static final String TABLE = "escolaridades";
    private static final String ID = "id";
    private static final String ID_GRADOACADEMICO = "grado_academico";
    private static final String FOLIO = "folio";
    private static final String INSTITUTO_EXPEDICION = "instituto_expedicion";
    private static final String CERTIFICADO = "certificado";
    private static final String PROMEDIO = "promedio";
    private static final String TITULO = "titulo";
    private static final String FECHA_TERMINO = "fecha_termino";

    private Escolaridad(int id, int idGradoAcademico, String folio, String institutoExpedicion, String certificado, double promedio, String titulo, String fechaTermino) {
        this.id = id;
        this.idGradoAcademico = idGradoAcademico;
        this.folio = folio;
        this.institutoExpedicion = institutoExpedicion;
        this.certificado = certificado;
        this.promedio = promedio;
        this.titulo = titulo;
        this.fechaTermino = fechaTermino;
    }

    public Escolaridad() {
        this(0, 0, "", "", "", 0, "", "");
    }
    
    public Escolaridad(int idGradoAcademinoc, String folio, String institutoExpedicion, String certificado, double promedio, String titulo, String fechaTermino)
    {
        this(0, idGradoAcademinoc, folio, institutoExpedicion, certificado, promedio, titulo, fechaTermino);
    }
    
    /**
     * Get a single cargo store in the database by searching it's id
     * in the table
     * @param id identifier that is going to be search
     * @return cargo instance initialize with the value of the database
     * if it's found the given id otherwise returns null
     * @throws SQLException if some db exception happend during this operation
     * this is throw again
     * @throws Exception if some exception happend during the execution of this
     * operation this is throw
     */
    public static Escolaridad getEscolaridad(int id) throws SQLException, Exception {
        Escolaridad escolaridad = null;
        String[] columns = new String[] { ID, ID_GRADOACADEMICO, FOLIO,
            INSTITUTO_EXPEDICION, CERTIFICADO, PROMEDIO, TITULO, FECHA_TERMINO };
        
        Map<String, String> where = new LinkedHashMap<String, String>();
        where.put(ID, new Integer(id).toString());
        
        List<Object> rawCargo = caracolQuery.query(columns, TABLE, where, null, null, null, null);
        
        if (rawCargo != null && rawCargo.size() > 0) {
            List<Object> row = (List<Object>) rawCargo.get(0);
            escolaridad  = new Escolaridad();
            escolaridad.id = id;
            escolaridad.setIdGradoAcademico(Integer.parseInt(row.get(1).toString()));
            escolaridad.setFolio(row.get(2).toString());
            escolaridad.setInstitutoExpedicion(row.get(3).toString());
            escolaridad.setCertificado(row.get(4).toString());
            escolaridad.setPromedio(new Double(row.get(5).toString()));
            escolaridad.setTitulo(row.get(6).toString());
            escolaridad.setFechaTermino(row.get(7).toString());
            
        }
        
        return escolaridad;
    }
    
    /**
     * Get all the cargos that exist in the database
     * @return {}
     * @throws SQLException
     * @throws Exception 
     */
    public static List<Escolaridad> getAll() throws SQLException, Exception {
        ArrayList<Escolaridad> escolaridades = new ArrayList<Escolaridad>();
        String[] columns = new String[] { ID, ID_GRADOACADEMICO, FOLIO,
            INSTITUTO_EXPEDICION, CERTIFICADO, PROMEDIO, TITULO, FECHA_TERMINO };
        List<Object> rawCargo = caracolQuery.query(columns, TABLE, null, null, null, null, null);
        
        for (Object object : rawCargo) {
            List<Object> row = (List<Object>) object;
            Escolaridad escolaridad = new Escolaridad();
            escolaridad.id = new Double(row.get(0).toString()).intValue();
            escolaridad.setIdGradoAcademico(Integer.parseInt(row.get(1).toString()));
            escolaridad.setFolio(row.get(2).toString());
            escolaridad.setInstitutoExpedicion(row.get(3).toString());
            escolaridad.setCertificado(row.get(4).toString());
            escolaridad.setPromedio(new Double(row.get(5).toString()));
            escolaridad.setTitulo(row.get(6).toString());
            escolaridad.setFechaTermino(row.get(7).toString());
            escolaridades.add(escolaridad);
        }
        
        return escolaridades;
    }
    
    /** 
     * Saves the values of this instance in the database
     * @return true if the operation affects more than 0 rows
     * @throws SQLException if some exception related with the database happend during the excetion 
     * of this operation
     * @throws Exception if some exception happend during the operation
     */
    public boolean save() throws SQLException, Exception {
        if (getId() > 0) { // this is already save
            return true;
        }
        Map<String,Object> m = new LinkedHashMap<String,Object>();
        m.put(ID_GRADOACADEMICO, getIdGradoAcademico());
        m.put(FOLIO, getFolio());
        m.put(INSTITUTO_EXPEDICION, getInstitutoExpedicion());
        m.put(CERTIFICADO, getCertificado());
        m.put(PROMEDIO, getPromedio());
        m.put(TITULO, getTitulo());
        m.put(FECHA_TERMINO, getFechaTermino());
        
        int rowsAffected = caracolQuery.insert(TABLE, m);
        
        ArrayList<Object> data = null;
        if (rowsAffected > 0) {
            AccesoDatos ad = caracolQuery.getOpenConnection();
            data = new ArrayList<Object>(ad.rawQuery("SELECT currval(\'sqescolaridades\');"));
        }
        if (data != null && data.size() > 0) {
            ArrayList<Object> row = (ArrayList<Object>) data.get(0);
            Double temp = Double.parseDouble(row.get(0).toString());
            this.id = temp.intValue();
            return true;
        }
        return false;
    }

    /**
     * Updates the database row with this instance values
     * @return true if rows affected is > 0
     * @throws SQLException is some db exception happen this is throw
     * @throws Exception is some exception happen this is throw
     */
    public boolean update() throws SQLException, Exception {
        int rowsAffected;
        Map<String, Object> values = new LinkedHashMap<String, Object>();
        values.put(ID_GRADOACADEMICO, getIdGradoAcademico());
        values.put(FOLIO, getFolio());
        values.put(INSTITUTO_EXPEDICION, getInstitutoExpedicion());
        values.put(CERTIFICADO, getCertificado());
        values.put(PROMEDIO, getPromedio());
        values.put(TITULO, getTitulo());
        values.put(FECHA_TERMINO, getFechaTermino());
        
        Map<String,Object> conditions = new LinkedHashMap<String, Object>();
        conditions.put(ID, getId());
        
        rowsAffected = caracolQuery.update(TABLE, values, conditions);
        
        return rowsAffected > 0;
    }
    
    /** 
     * Delete the cargo from database this delete is made by searching in the database if this instance id exist
     * 
     * @return true if the operation affect more thant one row otherwise returns false
     * @throws SQLException if the database throws an exception during the execution of this operation
     * @throws Exception if something other than the database throws an exception this is throw
     * 
     */
    public boolean delete() throws SQLException, Exception {
        if (getId() <= 0) {
            return true;
        }

        Map<String, Object> conditions = new LinkedHashMap<String, Object>();
        conditions.put(ID, getId());
        int rowsAffected = caracolQuery.delete(TABLE, conditions);

        if (rowsAffected > 0) {
            id = 0;
        }

        return rowsAffected > 0;
    }

    /**
     * @return the id
     */
    public int getId() {
        return id;
    }

    /**
     * @return the idGradoAcademico
     */
    public int getIdGradoAcademico() {
        return idGradoAcademico;
    }

    /**
     * @param idGradoAcademico the idGradoAcademico to set
     */
    public void setIdGradoAcademico(int idGradoAcademico) {
        this.idGradoAcademico = idGradoAcademico;
    }

    /**
     * @return the folio
     */
    public String getFolio() {
        return folio;
    }

    /**
     * @param folio the folio to set
     */
    public void setFolio(String folio) {
        this.folio = folio;
    }

    /**
     * @return the institutoExpedicion
     */
    public String getInstitutoExpedicion() {
        return institutoExpedicion;
    }

    /**
     * @param institutoExpedicion the institutoExpedicion to set
     */
    public void setInstitutoExpedicion(String institutoExpedicion) {
        this.institutoExpedicion = institutoExpedicion;
    }

    /**
     * @return the certificado
     */
    public String getCertificado() {
        return certificado;
    }

    /**
     * @param certificado the certificado to set
     */
    public void setCertificado(String certificado) {
        this.certificado = certificado;
    }

    /**
     * @return the promedio
     */
    public double getPromedio() {
        return promedio;
    }

    /**
     * @param promedio the promedio to set
     */
    public void setPromedio(double promedio) {
        this.promedio = promedio;
    }

    /**
     * @return the titulo
     */
    public String getTitulo() {
        return titulo;
    }

    /**
     * @param titulo the titulo to set
     */
    public void setTitulo(String titulo) {
        this.titulo = titulo;
    }

    /**
     * @return the fechaTermino
     */
    public String getFechaTermino() {
        return fechaTermino;
    }

    /**
     * @param fechaTermino the fechaTermino to set
     */
    public void setFechaTermino(String fechaTermino) {
        this.fechaTermino = fechaTermino;
    }
    
    
    
}
